Using a Distributed Transaction

The following code example, which uses the emp table (see "Sample Tables for Oracle"), shows how to use a distributed transaction to connect to two different Oracle servers.

NOTE: Microsoft Distributed Transaction Coordinator must be running on all clients and servers. The Oracle databases in this example do not require the Database connection string option.

using System; 
using System.EnterpriseServices; 
using DDTek.SequeLink; 
namespace DistributedTransaction  
{ 
    /// <summary> 
    /// Summary description for Class1. 
    /// </summary> 
    public class Class1  
    { 
       /// <summary> 
       /// The main entry point for the application. 
       /// </summary> 
       [STAThread] 
       static void Main(string[] args) { 
          SequeLinkConnection DBConn1; 
          DBConn1 = new SequeLinkConnection("host=norman;SID=test;Port=1521;
                                User ID=test01;Password=test01;Enlist=true"); 
          SequeLinkConnection DBConn2; 
          DBConn2 = new SequeLinkConnection("host=Carrie;SID=test;Port=1521;
                                User ID=test01;Password=test01;Enlist=true"); 
          try { 
             DBConn1.Open(); 
             DBConn2.Open(); 
             DistributedTran myDistributedTran = new DistributedTran(); 
             myDistributedTran.TestDistributedTransaction(DBConn1, DBConn2); 
             // Note, the connections used in distributed transaction must 
             // only be closed outside of transaction methods, because after  
             // these methods finish, DTC still needs these 
             // connections to complete commit/rollback commands. 
             DBConn1.Close(); 
             DBConn2.Close(); 
          } 
          catch (Exception e) { 
            System.Console.WriteLine("Error returned: " + e.Message); 
          } 
       } 
    } 
    /// <summary> 
    /// To use distributed transactions in .NET, we need a ServicedComponent  
    /// derived class with transaction attribute declared as "Required". 
    /// </summary> 
    [Transaction(TransactionOption.Required) ] 
    public class DistributedTran : ServicedComponent { 
       /// <summary> 
       /// This method executes two SQL statements, one on each database  
       /// server. If both are successful, both are commited by DTC after the  
       /// method finishes. However, if an exception is thrown, both will be 
       /// rolled back by DTC. 
       /// </summary> 
       [AutoComplete] 
       public void TestDistributedTransaction( 
          SequeLinkConnection DBConn1, 
          SequeLinkConnection DBConn2) { 
          // The following Insert statement goes to the first server, norman. 
          // This Insert statement does not produce any errors. 
          String DBCmdSql1 = "INSERT INTO emp VALUES 
                          (15,'HAYES','ADMIN',6,'17-NOV-2006',18000,NULL,4)"; 
          // The following Delete statement goes to the second server,  
          // Carrie. Because the Raises table does not exist on Carrie, 
          // the code throws an exception. 
          String DBCmdSql2 = "DELETE * FROM Raises WHERE sal > 100000"; 
          SequeLinkCommand DBCmd1 = new SequeLinkCommand(DBCmdSql1, DBConn1); 
          SequeLinkCommand DBCmd2 = new SequeLinkCommand(DBCmdSql2, DBConn2); 
          DBCmd1.ExecuteNonQuery(); 
          // This command results in an exception, which automatically rolls  
          // back the DBCmd1 command on the other server. 
          DBCmd2.ExecuteNonQuery(); 
       } 
    }  
}